<#assign object = doc.object>
package ${object.package}.service.base;
/**
* All the generated SQL statements for ${object.name} go in here.
* If you have custom queries, or you want to override existing ones, 
* best put them in here ${object.package}.service.${object.name}.
<#if object.trashcan[0]?? && object.trashcan == "true">
* <br>WARNING ${object.name} IMPLEMENTS TRASHCAN!!
* <br>
* Therefore, remember to suffix every where clause with 'AND roDeleted=false'.
</#if>
* @author persistence
* @see ${object.package}.service.${object.name}SQL
*/
public class ${object.name}SQLBase {

  /**
  * All the fields that are usually needed in them same order as defined in the model.
  */
  public final static String ALL_NONLAZY_FIELDS = "id," +
    <#list object.attributes?children as att>
       <#if att?node_name!="set" && att?node_name!="xml" && att?node_name!="html" && att?node_type=="element"><#t>
          <#if att?node_name=="object">
    "${att.@class?uncap_first}Id," +
          <#elseif !att.@lazy[0]?? || att.@lazy == "false">
    "${att.@name[0]!att.@class?uncap_first}," +
          </#if>
       </#if><#t>
    </#list>
    "creationDate,updateDate";

  /** delete from ${object.name} where id = ? */
  public final static String DELETE = "delete from ${object.name} where id = ?";

<#if object.trashcan[0]?? && object.trashcan == "true">
  /** where roDeleted = true */
  public final static String FIND_TRASH = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} where roDeleted = true";

  /** where roDeleted = true and id = ?*/
  public final static String FIND_TRASH_BY_ID = FIND_TRASH + " and id = ?";

  /** update ${object.name} set roDeleted = true where id = ? */
  public final static String TRASH = "update ${object.name} set roDeleted = ? where id = ?";

</#if>
  /**
  * where id = ?
<#if object.trashcan[0]?? && object.trashcan == "true">
  * and roDeleted = false<br />
</#if>
  */
  public final static String FIND_BY_ID = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} where id = ? <#if object.trashcan[0]?? && object.trashcan == "true">and roDeleted = false</#if>";

  /**
<#if object.trashcan[0]?? && object.trashcan == "true">
  * where roDeleted = false<br />
</#if>
  * order by 1 desc
  */
  public final static String FIND_ALL = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} <#if object.trashcan[0]?? && object.trashcan == "true">where roDeleted = false </#if>order by 1 desc";

<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id")) || att?node_name == "object">
    <@relation att=att />
  /**
  * where ${relation_name} = ?<br />
      <#if object.trashcan[0]?? && object.trashcan == "true">
  * and roDeleted = false<br />
      </#if>
  * order by 1
  */
  public static final String FIND_BY_${relation_name?upper_case} = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} where ${relation_name} = ? <#if object.trashcan[0]?? && object.trashcan == "true">and roDeleted = false </#if>order by 1";

  public static final String DELETE_BY_${relation_name?upper_case} = "delete ${object.name} where ${relation_name} = ?";

        <#if object.trashcan[0]?? && object.trashcan == "true">
  public static final String TRASH_BY_${relation_name?upper_case} = "update ${object.name} set roDeleted = ? where ${relation_name} = ?";
        </#if>
    </#if>
</#list>

<#list object.attributes.enum as att>
  /**
  * where ${att.@class?uncap_first} = ?<br />
    <#if object.trashcan[0]?? && object.trashcan == "true">
  * and roDeleted = false<br />
    </#if>
  * order by 1
  */
  public static final String FIND_BY_${att.@class?upper_case} = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} where ${att.@class?uncap_first} = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if> order by 1";
</#list>

<#list doc["/object/attributes/xml | /object/attributes/html"] as att>
  /**
  * where id = ?<br />
<#if object.trashcan[0]?? && object.trashcan == "true">
  * and roDeleted = false<br />
</#if>
  */
  public static final String FIND_${att.@name?upper_case}_BY_ID = "select ${att.@name} from ${object.name} where id = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>";
</#list>

<#list doc["/object/attributes/*[@finder='true']"] as att>
	/**
	* where ${att.@name} = ?<br />
<#if object.trashcan[0]?? && object.trashcan == "true">
    * and roDeleted = false<br />
</#if>
	* order by 1
	*/
	public static final String FIND_BY_${att.@name?upper_case} = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} where ${att.@name} = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if> order by 1";
</#list>

<#list doc["/object/attributes/*[@lazy='true']"] as att>
	<#if att?node_name == "string">
	public static final String FIND_${att.@name?upper_case}_BY_ID = "select ${att.@name} from ${object.name} where id = ?";
	</#if>
</#list>

  /** Create table statement for MySQL */
  public final static String CREATE_TABLE = "create table ${object.name} ("
    + "id bigint not null auto_increment, "
<#list object.attributes?children as att>
    <#if att?node_name != "set" && att?node_name != "object" && att?node_type == "element">
    + "${att.@name[0]!att.@class?uncap_first} <#t>
        <#if att?node_name == "boolean">
    tinyint(1) <#t>
        <#elseif att?node_name == "date">
    date <#t>
        <#elseif att?node_name == "datetime">
    datetime <#t>
        <#elseif att?node_name == "enum">
    enum(<#t>
            <#list att.element as value>
      '${value.@name}'<#if value_has_next>,</#if><#t>
            </#list>
    ) <#t>
        <#elseif att?node_name=="integer">
    int <#t>
        <#elseif att?node_name=="long">
    bigint <#t>
        <#elseif att?node_name=="string">
            <#if att.@maxlength?number &gt; 16000000>
    longtext <#t>
            <#elseif att.@maxlength?number &gt; 65000>
    mediumtext <#t>
            <#else>
    varchar(${att.@maxlength}) <#t>
            </#if>
        <#elseif att?node_name == "xml" || att?node_name == "html">
    longtext <#t>
        </#if>
        <#if att.@notnull[0]?? && att.@notnull == "true">
    not null <#t>
        </#if>
    ,"<#t>
    <#elseif att?node_name == "object">
    + "${att.@name[0]!att.@class?uncap_first}Id bigint <#t>
        <#if att.@notnull[0]?? && att.@notnull == "true">
    not null <#t>
        </#if>
    ,"<#t>
    </#if>
</#list>
<#if object.trashcan[0]?? && object.trashcan == "true">
    + "roDeleted boolean not null default false,"
</#if>
    + "creationDate timestamp not null, updateDate timestamp null,"
    + " primary key (id)"
<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id")) || att?node_name == "object" || att?node_name == "enum">
    + ", index ${object.name}_${att.@name[0]!att.@class?uncap_first}<#if att?node_name == "object">Id</#if> (${att.@name[0]!att.@class?uncap_first}<#if att?node_name == "object">Id</#if>)"
    </#if>
</#list>
<#list doc["/object/attributes/*[@finder='true']"] as att>
    + ", index ${object.name}_${att.@name} (${att.@name})"
</#list>
    + ")";<#t>
	
	//TODO not needed when we have pagination 
  /** Test FIND_ALL + limit 10 */
  public static final String TEST = FIND_ALL + " limit 10";
	
<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
	<@relation_table att=att />
  /** Create table of set statement for MySQL */
  public final static String CREATE_${relation_table_name?upper_case}_TABLE = "create table ${relation_table_name} ("
    + "id bigint not null auto_increment,"
    + "${object.name}Id bigint not null,"
    + "${att.@class}Id bigint not null,"
<#if object.trashcan[0]?? && object.trashcan == "true">
	+ "roDeleted boolean not null default false,"
</#if>
    + "creationDate timestamp not null,updateDate timestamp null,"
    + "primary key (id)"
    + ", index ${relation_table_name}_${object.name}Id (${object.name}Id)"
    + ", index ${relation_table_name}_${att.@class}Id (${att.@class}Id)"    
    + ")";
  
  /** delete from ${relation_table_name?upper_case} where ${object.name}Id = ? and ${att.@class}Id = ? */
  public final static String DELETE_${relation_table_name?upper_case}_RELATION = "delete from ${relation_table_name} where ${object.name}Id = ? and ${att.@class}Id = ?";

<#if object.trashcan[0]?? && object.trashcan == "true">
  public final static String TRASH_${relation_table_name?upper_case}_RELATION = "update ${relation_table_name?upper_case} set roDeleted = true where ${object.name}Id = ? and ${att.@class}Id = ?";
</#if>
  
  public final static String DELETE_${relation_table_name?upper_case}_RELATION_BY_${object.name?upper_case}ID = "delete from ${relation_table_name} where ${object.name}Id = ?";

<#if object.trashcan[0]?? && object.trashcan == "true">
  public final static String TRASH_${relation_table_name?upper_case}_RELATION_BY_${object.name?upper_case}ID = "update ${relation_table_name} set roDeleted = ? where ${object.name}Id = ?";
</#if>
  
  public final static String INSERT_${relation_table_name?upper_case}_RELATION = "insert into ${relation_table_name} (${object.name}Id,${att.@class}Id,creationDate) values (?,?,now())";
  
  /** where ${att.@class}Id = ?<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>*/
  public final static String FIND_${object.name?upper_case}_BY_${att.@class?upper_case}ID = "select " + ALL_NONLAZY_FIELDS + " from ${object.name} where id in (select ${object.name}Id from ${relation_table_name} where ${att.@class}Id = ?)<#if object.trashcan[0]?? && object.trashcan == "true"> and roDeleted = false</#if>";
</#list>
}
